package com.petsystem.dao.impl;

import com.petsystem.util.Util;
import com.petsystem.dao.PetDAO;
import com.petsystem.vo.Pet;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 宠物模块的DAO层（数据层）的具体实现类，对PetDAO接口中定义的增删改查等抽象方法作出具体的功能实现
 */
public class PetDAOImpl implements PetDAO {

    @Override
    public void add(Pet vo) {
        String sql = "insert into `t_pet` (`pet_name`,`pet_type`,`pet_sex`,`pet_age`,`pet_master`,`pet_master_phone`,`pet_text`,`create_by`) values(?,?,?,?,?,?,?,?)";
        try {
            Connection c = Util.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, vo.getPetName());
            ps.setString(2, vo.getPetType());
            ps.setString(3, vo.getPetSex());
            ps.setString(4, vo.getPetAge());
            ps.setString(5, vo.getPetMaster());
            ps.setString(6, vo.getPetMasterPhone());
            ps.setString(7, vo.getPetText());
            ps.setLong(8, vo.getCreateBy() != null ? vo.getCreateBy() : 0L);
            ps.execute();
            ps.close();
            c.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(Pet vo) {
        String sql = "update `t_pet` set `pet_name` = ? ,`pet_type` = ? ,`pet_sex` = ? ,`pet_age` = ? ,`pet_master` = ? ,`pet_master_phone` = ? ,`pet_text` = ? ,`create_by` = ?  where `id` = ?";
        try {
            Connection c = Util.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, vo.getPetName());
            ps.setString(2, vo.getPetType());
            ps.setString(3, vo.getPetSex());
            ps.setString(4, vo.getPetAge());
            ps.setString(5, vo.getPetMaster());
            ps.setString(6, vo.getPetMasterPhone());
            ps.setString(7, vo.getPetText());
            ps.setLong(8, vo.getCreateBy());
            ps.setLong(9, vo.getId());
            ps.execute();
            ps.close();
            c.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public boolean delete(long id) {
        try {
            Connection c = Util.getConnection();
            Statement s = c.createStatement();
            String sql = "delete from `t_pet` where id = " + id;
            s.execute(sql);
            s.close();
            c.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public Pet get(Serializable id) {
        Pet vo = null;
        try {
            Connection c = Util.getConnection();
            Statement s = c.createStatement();
            String sql = "select * from `t_pet` where id = " + id;
            ResultSet rs = s.executeQuery(sql);
            if (rs.next()) {
                vo = new Pet();
                vo.setId(rs.getLong("id"));
                vo.setPetName(rs.getString("pet_name"));
                vo.setPetType(rs.getString("pet_type"));
                vo.setPetSex(rs.getString("pet_sex"));
                vo.setPetAge(rs.getString("pet_age"));
                vo.setPetMaster(rs.getString("pet_master"));
                vo.setPetMasterPhone(rs.getString("pet_master_phone"));
                vo.setPetText(rs.getString("pet_text"));
                vo.setCreateBy(rs.getLong("create_by"));
            }
            c.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return vo;
    }

    @Override
    public List<Pet> list(Map<String, Object> params) {
        List<Pet> list = new ArrayList<>();
        String condition = "";
        if (params.get("searchColumn") != null && !"".equals(params.get("searchColumn"))) {
            condition += " and `" + params.get("searchColumn") + "` like '%" + params.get("keyword") + "%'";
        }
        if (params.get("createBy") != null && !"".equals(params.get("createBy"))) {
            condition += " and `create_by` = " + params.get("createBy");
        }
        if (params.get("id") != null) {
            condition += " and `id` = " + params.get("id");
        }
        try {
            Connection c = Util.getConnection();
            String sqlList = "select * from `t_pet` where 1=1 " + condition + " order by id asc;";
            PreparedStatement ps = c.prepareStatement(sqlList);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Pet vo = new Pet();
                vo.setId(rs.getLong("id"));
                vo.setPetName(rs.getString("pet_name"));
                vo.setPetType(rs.getString("pet_type"));
                vo.setPetSex(rs.getString("pet_sex"));
                vo.setPetAge(rs.getString("pet_age"));
                vo.setPetMaster(rs.getString("pet_master"));
                vo.setPetMasterPhone(rs.getString("pet_master_phone"));
                vo.setPetText(rs.getString("pet_text"));
                vo.setCreateBy(rs.getLong("create_by"));
                list.add(vo);
            }
            rs.close();
            ps.close();
            c.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}